package conchmusic.conchmusicclient.dao;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import java.util.ArrayList;
import java.util.List;

import conchmusic.conchmusicclient.pojo.Artist;
import conchmusic.conchmusicclient.pojo.Song;
import conchmusic.conchmusicclient.pojo.SongList;
import conchmusic.conchmusicclient.pojo.User;

/**
 * Created by 76706 on 2017/4/22.
 */

public class OperateDB implements InterfaceDB{
    private SQLiteDatabase db;
    private MyDBHelp help;

    public OperateDB(MyDBHelp help) {
        this.help = help;
    }

    @Override
    public int findArtist(String artistName) {
        int a=0;
        db=help.getReadableDatabase();
        String sql="select localArtistId from artist where artistName=?";
        Cursor c=db.rawQuery(sql,new String[]{artistName});
        if (c.moveToNext()){
            a=c.getInt(c.getColumnIndex("localArtistId"));
        }
        return a;
    }

    @Override
    public boolean findArtist_Song(int localArtistId, int localSongId) {
        boolean a=false;
        db=help.getReadableDatabase();
        String sql="select * from artist_song where localArtistId=? and localSongId=?";
        Cursor c=db.rawQuery(sql,new String[]{localArtistId+"",localSongId+""});
        if(c.moveToNext()){
            a=true;
        }
        return a;
    }

    @Override
    public int findLocalSongId(String title, String albumName) {
        int a=0;
        db=help.getReadableDatabase();
        String sql="select localSongId from song where title=? and albumName=?";
        Cursor c=db.rawQuery(sql,new String[]{title,albumName});
        if (c.moveToNext()){
            a=c.getInt(c.getColumnIndex("localSongId"));
        }
        return a;
    }

    @Override
    public int findLocalSongId(int songId) {
        int a=0;
        db=help.getReadableDatabase();
        String sql="select localSongId from song where songId=?";
        Cursor c=db.rawQuery(sql,new String[]{songId+""});
        if (c.moveToNext()){
            a=c.getInt(c.getColumnIndex("localSongId"));
        }
        return a;
    }

    @Override
    public User findUser() {
        User user=null;
        db=help.getReadableDatabase();
        String sql="select * from user where isLogin>0";
        Cursor c=db.rawQuery(sql,new String[]{});
        if(c.moveToNext()){
            user=new User(c.getInt(c.getColumnIndex("userId")),c.getString(c.getColumnIndex("userName")),c.getString(c.getColumnIndex("password")),c.getString(c.getColumnIndex("email")),c.getString(c.getColumnIndex("createdTime")),c.getString(c.getColumnIndex("modifiedTime")),c.getInt(c.getColumnIndex("isLogin")));
        }
        return user;
    }

    @Override
    public User findUser(int userId) {
        User user=null;
        db=help.getReadableDatabase();
        String sql="select * from user where userId=?";
        Cursor c=db.rawQuery(sql,new String[]{userId+""});
        if(c.moveToNext()){
            user=new User(userId,c.getString(c.getColumnIndex("userName")),c.getString(c.getColumnIndex("password")),c.getString(c.getColumnIndex("email")),c.getString(c.getColumnIndex("createdTime")),c.getString(c.getColumnIndex("modifiedTime")),c.getInt(c.getColumnIndex("isLogin")));
        }

        return user;
    }

    @Override
    public int findSongList(int songListId) {
        int a=0;
        db=help.getReadableDatabase();
        String sql="select songListId from songList where songListId=?";
        Cursor c=db.rawQuery(sql,new String[]{songListId+""});
        if(c.moveToNext()){
            a=c.getInt(c.getColumnIndex("songListId"));
        }
        return a;
    }

    @Override
    public int findSongList(String songListName, int userId) {
        int a=0;
        db=help.getReadableDatabase();
        String sql="select localSongId from song where songListName=? and userId=?";
        Cursor c=db.rawQuery(sql,new String[]{songListName,userId+""});
        if(c.moveToNext()){
            a=c.getInt(c.getColumnIndex("localSongId"));
        }
        return a;
    }

    @Override
    public boolean findSong_SongList(int songListId, int localSongId) {
        boolean a=false;
        db=help.getReadableDatabase();
        String sql="select * from song_songList where songListId=? and localSongId=?";
        Cursor c=db.rawQuery(sql,new String[]{songListId+"",localSongId+""});
        if(c.moveToNext()){
            a=true;
        }
        return a;
    }

    @Override
    public List<Song> findSongs(int songListId) {
        db=help.getReadableDatabase();
        List<Song> songs=new ArrayList<>();
        String sql="select song.* from song,song_songList where song.localSongId=song_songList.localSongId and songListId=?";
        try {
            Cursor c=db.rawQuery(sql,new String[]{songListId+""});
            while (c.moveToNext()){
                SQLiteDatabase db2=help.getReadableDatabase();
                List<Integer> artistIds = new ArrayList<>();
                List<String> artistNames = new ArrayList<>();
                String sql2="select localArtistId,artistName from artist where localArtistId in (select localArtistId from artist_song where localSongId=?)";
                Cursor c2=db2.rawQuery(sql2,new String[]{c.getInt(c.getColumnIndex("localSongId"))+""});
                while (c2.moveToNext()){
                    artistIds.add(c2.getInt(c2.getColumnIndex("localArtistId")));
                    artistNames.add(c2.getString(c2.getColumnIndex("artistName")));
                }
                Song song=new Song(c.getInt(c.getColumnIndex("localSongId")),c.getInt(c.getColumnIndex("songId")),c.getString(c.getColumnIndex("title")),c.getInt(c.getColumnIndex("albumId")),c.getString(c.getColumnIndex("albumName")),artistIds,artistNames,c.getString(c.getColumnIndex("songUrl")),c.getString(c.getColumnIndex("localSongUrl")),c.getString(c.getColumnIndex("lyricUrl")),c.getString(c.getColumnIndex("localLyricUrl")),c.getString(c.getColumnIndex("pictureUrl")),c.getString(c.getColumnIndex("localPictureUrl")));
                songs.add(song);
            }
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            db.close();
        }
        return songs;
    }

    @Override
    public List<Song> findSongs(int songListId, String title) {
        db=help.getReadableDatabase();
        List<Song> songs=new ArrayList<>();
        String sql="select song.* from song,song_songList where song.localSongId=song_songList.localSongId and songListId=? and title like ?";
        try {
            Cursor c=db.rawQuery(sql,new String[]{songListId+"","%"+title+"%"});
            while (c.moveToNext()){
                SQLiteDatabase db2=help.getReadableDatabase();
                List<Integer> artistIds = new ArrayList<>();
                List<String> artistNames = new ArrayList<>();
                String sql2="select localArtistId,artistName from artist where localArtistId in (select localArtistId from artist_song where localSongId=?)";
                Cursor c2=db2.rawQuery(sql2,new String[]{c.getInt(c.getColumnIndex("localSongId"))+""});
                while (c2.moveToNext()){
                    artistIds.add(c2.getInt(c2.getColumnIndex("localArtistId")));
                    artistNames.add(c2.getString(c2.getColumnIndex("artistName")));
                }
                Song song=new Song(c.getInt(c.getColumnIndex("localSongId")),c.getInt(c.getColumnIndex("songId")),c.getString(c.getColumnIndex("title")),c.getInt(c.getColumnIndex("albumId")),c.getString(c.getColumnIndex("albumName")),artistIds,artistNames,c.getString(c.getColumnIndex("songUrl")),c.getString(c.getColumnIndex("localSongUrl")),c.getString(c.getColumnIndex("lyricUrl")),c.getString(c.getColumnIndex("localLyricUrl")),c.getString(c.getColumnIndex("pictureUrl")),c.getString(c.getColumnIndex("localPictureUrl")));
                songs.add(song);
            }
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            db.close();
        }
        return songs;
    }

    @Override
    public Song findSong(int localSongId) {
        db=help.getReadableDatabase();
        Song song=null;
        String sql="select * from song where localSongId=?";
        try{
            Cursor c=db.rawQuery(sql,new String[]{localSongId+""});
            while (c.moveToNext()){
                SQLiteDatabase db2=help.getReadableDatabase();
                List<Integer> artistIds = new ArrayList<>();
                List<String> artistNames = new ArrayList<>();
                String sql2="select localArtistId,artistName from artist where localArtistId in (select localArtistId from artist_song where localSongId=?)";
                Cursor c2=db2.rawQuery(sql2,new String[]{c.getInt(c.getColumnIndex("localSongId"))+""});
                while (c2.moveToNext()){
                    artistIds.add(c2.getInt(c2.getColumnIndex("localArtistId")));
                    artistNames.add(c2.getString(c2.getColumnIndex("artistName")));
                }
                song=new Song(c.getInt(c.getColumnIndex("localSongId")),c.getInt(c.getColumnIndex("songId")),c.getString(c.getColumnIndex("title")),c.getInt(c.getColumnIndex("albumId")),c.getString(c.getColumnIndex("albumName")),artistIds,artistNames,c.getString(c.getColumnIndex("songUrl")),c.getString(c.getColumnIndex("localSongUrl")),c.getString(c.getColumnIndex("lyricUrl")),c.getString(c.getColumnIndex("localLyricUrl")),c.getString(c.getColumnIndex("pictureUrl")),c.getString(c.getColumnIndex("localPictureUrl")));
            }
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            db.close();
        }
        return song;
    }

    @Override
    public List<SongList> findSongLists(int userId) {
        List<SongList> lists=new ArrayList<>();
        db=help.getReadableDatabase();
        String sql="select * from songList where userId=?";
        try {

            Cursor c=db.rawQuery(sql,new String[]{userId+""});
            while(c.moveToNext()) {
                SQLiteDatabase db2 = help.getReadableDatabase();
                List<Integer> localSongIds = new ArrayList<>();
                String sql2 = "select localSongId from song_songList where songListId=?";
                Cursor c2 = db2.rawQuery(sql2, new String[]{c.getInt(c.getColumnIndex("songListId")) + ""});
                while (c2.moveToNext()) {
                    localSongIds.add(c2.getInt(c2.getColumnIndex("localSongId")));
                }
                SongList songList = new SongList(c.getInt(c.getColumnIndex("songListId")), userId, localSongIds, c.getString(c.getColumnIndex("songListName")), c.getString(c.getColumnIndex("createdTime")), c.getString(c.getColumnIndex("description")), c.getString(c.getColumnIndex("modifiedTime")));
                lists.add(songList);
            }
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            db.close();
        }
        return lists;
    }

    @Override
    public boolean addArtist(Artist artist) {
        boolean a=true;
        db=help.getWritableDatabase();
        Object[] obj=new Object[]{artist.getArtistId(),artist.getArtistName(),artist.getDescription()};
        String sql="insert into artist(artistId,artistName,description)values(?,?,?)";
        try{
            db.execSQL(sql,obj);
        }catch (Exception e) {
            e.printStackTrace();
            a=false;
        }finally {
            db.close();
        }
        return a;
    }

    @Override
    public boolean addSong(Song song) {
        boolean a=true;
        db=help.getWritableDatabase();
        Object[] obj=new Object[]{song.getSongId(),song.getTitle(),song.getAlbumId(),song.getAlbumName(),song.getSongUrl(),song.getLocalSongUrl(),song.getLyricUrl(),song.getLocalLyricUrl(),song.getPictureUrl(),song.getLocalPictureUrl()};
        String sql="insert into song(songId,title,albumId,albumName,songUrl,localSongUrl,lyricUrl,localLyricUrl,pictureUrl,localPictureUrl)values(?,?,?,?,?,?,?,?,?,?)";
        try{
            db.execSQL(sql,obj);
        }catch (Exception e) {
            e.printStackTrace();
            a=false;
        }finally {
            db.close();
        }
        return a;
    }

    @Override
    public boolean addArtist_Song(int localArtistId, int localSongId) {
        boolean a=true;
        db=help.getWritableDatabase();
        Object[] obj=new Object[]{localArtistId,localSongId};
        String sql="insert into artist_song(localArtistId,localSongId)values(?,?)";
        try{
            db.execSQL(sql,obj);
        }catch (Exception e) {
            e.printStackTrace();
            a=false;
        }finally {
            db.close();
        }
        return a;
    }

    @Override
    public boolean addUser(User user) {
        boolean a=true;
        db=help.getWritableDatabase();
        Object[] obj=new Object[]{user.getUserId(),user.getUserName(),user.getPassword(),user.getEmail(),user.getCreatedTime(),user.getModifiedTime(),user.getIsLogin()};
        String sql="insert into user(userId,userName,password,email,createdTime,modifiedTime,isLogin)values(?,?,?,?,?,?,?)";
        try{
            db.execSQL(sql,obj);
        }catch (Exception e) {
            e.printStackTrace();
            a=false;
        }finally {
            db.close();
        }
        return a;
    }

    @Override
    public boolean addSongList(SongList songList) {
        boolean a=true;
        db=help.getWritableDatabase();
        Object[] obj=new Object[]{songList.getSongListId(),songList.getSongListName(),songList.getUserId(),songList.getDescription(),songList.getCreatedTime(),songList.getModifiedTime()};
        String sql="insert into songList(songListId,songListName,userId,description,createdTime,modifiedTime)values(?,?,?,?,?,?)";
        try{
            db.execSQL(sql,obj);
        }catch (Exception e) {
            e.printStackTrace();
            a=false;
        }finally {
            db.close();
        }
        return a;
    }

    @Override
    public boolean addSong_SongList(int songListId, int localSongId) {
        boolean a=true;
        db=help.getWritableDatabase();
        Object[] obj=new Object[]{songListId,localSongId};
        String sql="insert into song_songList(songListId,localSongId)values(?,?)";
        try{
            db.execSQL(sql,obj);
        }catch (Exception e) {
            e.printStackTrace();
            a=false;
        }finally {
            db.close();
        }
        return a;
    }

    @Override
    public void deleteSong(int localSongId, int songListId) {

    }

    @Override
    public void deleteSong(int localSongId) {
        db=help.getWritableDatabase();
        Object[] obj=new Object[]{localSongId};
        String sql="delete from song where localSongId=?";
        try{
            db.execSQL(sql,obj);
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            db.close();
        }
    }

    @Override
    public void deleteSong() {
        db=help.getWritableDatabase();
        Object[] obj=new Object[]{};
        String sql="delete from song where songId>0";
        try{
            db.execSQL(sql,obj);
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            db.close();
        }
    }

    @Override
    public void deleteSongList(int songListId) {
        db=help.getWritableDatabase();
        Object[] obj=new Object[]{songListId};
        String sql="delete from songList where songListId =?";
        try{
            db.execSQL(sql,obj);
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            db.close();
        }
    }

    @Override
    public boolean deleteSongList() {
        boolean a=true;
        db=help.getWritableDatabase();
        Object[] obj=new Object[]{};
        String sql="delete from songList where songListId >2";
        try{
            db.execSQL(sql,obj);
        }catch (Exception e) {
            e.printStackTrace();
            a=false;
        }finally {
            db.close();
        }
        return a;
    }

    @Override
    public boolean deleteSong_SongList(int localSongId, int songListId) {
        boolean a=true;
        db=help.getWritableDatabase();
        Object[] obj=new Object[]{localSongId,songListId};
        String sql="delete from song_songList where localSongId=? and songListId = ?";
        try{
            db.execSQL(sql,obj);
        }catch (Exception e) {
            e.printStackTrace();
            a=false;
        }finally {
            db.close();
        }
        return a;
    }

    @Override
    public boolean deleteSong_SongList() {
        boolean a=true;
        db=help.getWritableDatabase();
        Object[] obj=new Object[]{};
        String sql="delete from song_songList where songListId in (select songListId from songList where songListId>2)";
        try{
            db.execSQL(sql,obj);
        }catch (Exception e) {
            e.printStackTrace();
            a=false;
        }finally {
            db.close();
        }
        return a;
    }

    @Override
    public void deleteArtist(int localArtistId) {
        db=help.getWritableDatabase();
        Object[] obj=new Object[]{localArtistId};
        String sql="delete from artist where localArtistId =?";
        try{
            db.execSQL(sql,obj);
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            db.close();
        }
    }

    @Override
    public void deleteArtist_Song(int localArtistId, int localSongId) {
        db=help.getWritableDatabase();
        Object[] obj=new Object[]{localArtistId,localSongId};
        String sql="delete from artist_song where localArtistId =? and localSongId=?";
        try{
            db.execSQL(sql,obj);
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            db.close();
        }
    }

    @Override
    public void deleteArtist_Song() {
        db=help.getWritableDatabase();
        Object[] obj=new Object[]{};
        String sql="delete from artist_song where localArtistId in (select localArtistId from artist where artistId>0)";
        try{
            db.execSQL(sql,obj);
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            db.close();
        }
    }

    @Override
    public void deleteArtist() {
        db=help.getWritableDatabase();
        Object[] obj=new Object[]{};
        String sql="delete from artist where artistId>0";
        try{
            db.execSQL(sql,obj);
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            db.close();
        }
    }

    @Override
    public boolean updateSongList(SongList songList) {
        boolean a=true;
        db=help.getWritableDatabase();
        Object[] obj=new Object[]{songList.getSongListName(),songList.getDescription(),songList.getCreatedTime(),songList.getModifiedTime(),songList.getSongListId()};
        String sql="update songList set songListName=?,description=?,createdTime=?,modifiedTime=? where songListId=?";
        try{
            db.execSQL(sql,obj);
        }catch (Exception e) {
            e.printStackTrace();
            a=false;
        }finally {
            db.close();
        }
        return a;
    }

    @Override
    public boolean updateSong(Song song) {
        boolean a=true;
        db=help.getWritableDatabase();
        Object[] obj=new Object[]{song.getTitle(),song.getAlbumId(),song.getAlbumName(),song.getSongUrl(),song.getLocalSongUrl(),song.getLyricUrl(),song.getLocalLyricUrl(),song.getPictureUrl(),song.getLocalPictureUrl(),song.getLocalSongId()};
        String sql="update song set title=?,albumId=?,albumName=?,songUrl=?,localSongUrl=?,lyricUrl=?,localLyricUrl=?,pictureUrl=?,localPictureUrl=? where localSongId=?";
        try{
            db.execSQL(sql,obj);
        }catch (Exception e) {
            e.printStackTrace();
            a=false;
        }finally {
            db.close();
        }
        return a;
    }

    @Override
    public boolean deleteSongUrl(int localSongId) {
        boolean a=true;
        db=help.getWritableDatabase();
        Object[] obj=new Object[]{localSongId};
        String sql="update song set localSongUrl=null,localLyricUrl=null,localPictureUrl=null where localSongId=?";
        try{
            db.execSQL(sql,obj);
        }catch (Exception e) {
            e.printStackTrace();
            a=false;
        }finally {
            db.close();
        }
        return a;
    }

    @Override
    public boolean updateUser(User user) {
        boolean a=true;
        db=help.getWritableDatabase();
        Object[] obj=new Object[]{user.getUserName(),user.getPassword(),user.getEmail(),user.getCreatedTime(),user.getModifiedTime(),user.getIsLogin(),user.getUserId()};
        String sql="update user set userName=?,password=?,email=?,createdTime=?,modifiedTime=?,isLogin=? where userId=?";
        try{
            db.execSQL(sql,obj);
        }catch (Exception e) {
            e.printStackTrace();
            a=false;
        }finally {
            db.close();
        }
        return a;
    }

    @Override
    public boolean updateArtist(Artist artist) {
        boolean a=true;
        db=help.getWritableDatabase();
        Object[] obj=new Object[]{artist.getArtistId(),artist.getArtistName(),artist.getDescription(),artist.getLocalArtistId()};
        String sql="update artist set artistId=?,artistName=?,description=? where localArtistId=?";
        try{
            db.execSQL(sql,obj);
        }catch (Exception e) {
            e.printStackTrace();
            a=false;
        }finally {
            db.close();
        }
        return a;
    }

    @Override
    public boolean logout(int userId) {
        boolean a=true;
        db=help.getWritableDatabase();
        Object[] obj=new Object[]{userId};
        String sql="update user set isLogin=0 where userId=?";
        try{
            db.execSQL(sql,obj);
        }catch (Exception e) {
            e.printStackTrace();
            a=false;
        }finally {
            db.close();
        }
        return a;
    }
}
